﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.IO;

namespace DIT.DB.Database
{
    public abstract class DatabaseBase
    {
        protected bool isFirstTime = false;
        protected readonly string dataSource;
        protected readonly string userId;
        protected readonly string password;
        protected readonly string startupScriptPath;
        protected DatabaseBase(string dataSource, string userId, string password, string startupScriptPath)
        {
            this.dataSource = dataSource;
            this.userId = userId;
            this.password = password;
            this.startupScriptPath = startupScriptPath;
        }
        protected void Setup()
        {
            if (isFirstTime)
            {
                //Install the Database Server
                //Create Database
                //Check the Connection
                //Run initial script if any
                if (!string.IsNullOrWhiteSpace(startupScriptPath))
                    RunScript(startupScriptPath);

                isFirstTime = false;
            }
        }

        //Exposed Method
        public void RunScript(string scriptFilePath)
        {
            FileInfo file = new FileInfo(scriptFilePath);
            string script = file.OpenText().ReadToEnd();
            string[] commandTextArray = script.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);


            IDbConnection connection = null;
            IDbTransaction transaction = null;
            try
            {
                connection = CreateConnection();
                connection.Open();
                transaction = connection.BeginTransaction();

                foreach (string queryString in commandTextArray)
                {
                    using (IDbCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Transaction = transaction;
                        cmd.CommandText = queryString;

                        cmd.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Close();
            }
        }
        public IDbCommand CreateCommand()
        {
            return CreateConnection().CreateCommand();
        }
        /// <summary>
        /// This method is used for select query
        /// </summary>
        /// <param name="command">The command object should be populated with query and necessary parameters</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(IDbCommand command)
        {
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// This method is used for any DML statement i.e. CREATE, INSERT, UPDATE, DELETE etc.
        /// </summary>
        /// <param name="command">The command object should be populated with query and necessary parameters</param>
        /// <returns>Returns how many rows are affected</returns>
        public int ExecuteNonQuery(IDbCommand command)
        {
            using (IDbConnection connection = command.Connection)
            {
                connection.Open();
                return command.ExecuteNonQuery();
            }
        }

        //Instance Specific Method
        protected abstract IDbConnection CreateConnection();
        protected abstract string ConnectionString { get; }
    }
}
